import pymysql
from todo import Todo
class TodoManagerWithMysql:

    @staticmethod
    def get_connection():
        return pymysql.Connection(host='192.168.204.129',database='test',user='root',password='root@123ABC',autocommit=True)

    def init_db(self):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute('''
            create table if not exists todos(
            todo_id int AUTO_INCREMENT primary key,
            title varchar(100) not null,
            completed int DEFAULT 0
            )
            ''')

    def create(self,title: str):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("insert into todos(title) values(%s)",(title,))
            cursor.execute("select last_insert_id()")
           # conn.commit()
            (todo_id,) = cursor.fetchone()
            return Todo(title,id = todo_id)

    def convert_row_to_object(self,row):
        return Todo(row[1],id=row[0],completed=True if row[2] == 1 else False)

    def get_list(self):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("select * from todos")
            todo = cursor.fetchall()
            todo_list = []
            for row in todo:
                todo_list.append(self.convert_row_to_object(row))
            return todo_list

    def get_by_id(self,todo_id):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("select * from todos where todo_id = %s",(todo_id,))
            todo = cursor.fetchone()
            if todo is not None:
                return self.convert_row_to_object(todo)
            else:
                return None

    def mark_completed(self,todo_id):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("update todos set completed = 1 where todo_id = %s",(todo_id,))
            return self.get_by_id(todo_id)
    def delete(self,todo_id):
        with self.get_connection() as conn:
            cursor=conn.cursor()
            cursor.execute("delete from todos where todo_id=%s",(todo_id,))
            return self.get_by_id(todo_id)
#manager = TodoManagerWithMysql()
#manager.create('学习python')
